-- @Create_time :2023-08-28 10:00
-- @Author      :kumiler
-- @emial       :lukunming@jtexpress.com
-- @File        :execute.hql
-- @Desc        :

-- 中心应付派费明细
WITH t1 AS (
    select waybill_no,
           sign_time,
           waybill_time,
           piece_network_code,
           send_network_code,
           create_time,
           fee_type_id,
           sender_province_id,
           sender_province_name,
           waybill_num,
           fee_cost,
           sum(red_sign) as red_sign
    from spmi_dwd.dwd_spmi_piece_bill_hi
    where dt >= concat(date_add('{{ execution_date | cst_ds }}',-65), '-00')
      and dt <= concat(date_add('{{ execution_date | cst_ds }}',1), '-00')
--       and piece_financial_center_code in (
--                                     '320002',  -- 苏南代理区
--                                     '320001',  -- 苏北代理区
--                                     '510000'   -- 川藏代理区
--         )
      and fee_type_id in ('203', '205')
    group by waybill_no,
             sign_time,
             waybill_time,
             piece_network_code,
             send_network_code,
             create_time,
             fee_type_id,
             sender_province_id,
             sender_province_name,
             waybill_num,
             fee_cost
),
     t2 as (
         select waybill_no,
                sign_time,
                waybill_time,
                piece_network_code,
                send_network_code,
                create_time,
                fee_type_id,
                sender_province_id,
                sender_province_name,
                sum(if(fee_type_id = '203' , fee_cost, 0.0))     as BF006,
                sum(if(fee_type_id = '205' , fee_cost, 0.0))     as BF012,
                waybill_num
         from t1
         where red_sign <> 0
         group by waybill_no, sign_time, waybill_time,
                  piece_network_code, send_network_code, create_time
                 , fee_type_id,sender_province_id,sender_province_name,waybill_num
     )
insert overwrite table spmi_dm.dm_spmi_center_piece_fee_report_detail_dt partition (dt)
select
detail.waybill_no,
detail.waybill_time,
detail.sign_time,
detail.piece_network_code,
nwm.name as piece_network_name,
nwm.fran_code as piece_franchisee_code,
nwm.fran_name as piece_franchisee_name,
nwm.financial_center_code as piece_financial_center_code,
nwm.financial_center_desc as piece_financial_center_name,
detail.sender_province_id,
detail.sender_province_name,
detail.BF006,
detail.BF012,
detail.waybill_num,
date_format(detail.sign_time,'yyyy-MM-dd') as dt
from t2 detail
left join jms_dim.dim_network_whole_massage nwm
on detail.piece_network_code = nwm.code
where date_format(detail.sign_time,'yyyy-MM-dd') between date_add('{{ execution_date | cst_ds }}',-50) and '{{ execution_date | cst_ds }}'
distribute by dt,pmod(hash(rand()),64)
;

